﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ArticleDBConnection$" formatter="bizobj of Counter">
	<Sql>
		SELECT count(a.id) as count
		FROM            
			Articles AS a 
			LEFT OUTER JOIN	ObjectTreeNodes AS t ON a.id = t.objectId
			LEFT OUTER JOIN ObjectTreeCounts AS nc on nc.objectId = t.objectId
		WHERE
			<code>
				<![CDATA[
			bool addAnd = true;
			bool entireTree = Convert.ToBoolean(info.get("entireTree"));
			bool includeSelf = Convert.ToBoolean(info.get("includeSelf"));
			bool parentIdProvided = info.get("parentId") != null;
			if(parentIdProvided)
			{
				if(entireTree && includeSelf)
					sql.Append("t.parentHostId = @parentId");	
				else if(entireTree && !includeSelf)
					sql.Append("(t.parentHostId = @parentId AND t.id <> @parentId)");
				else if(!entireTree && includeSelf)
					sql.Append("(t.parentId = @parentId OR t.id = @parentId)");
				else if(!entireTree && !includeSelf)
					sql.Append("t.parentId = @parentId AND t.id <> @parentId");
			}
			else if(!entireTree)
				sql.Append("t.id = nc.rootId");
			else
				addAnd = false;
			if(addAnd)
				sql.Append(" AND ");
			]]></code>

			<code>
				<![CDATA[
				int startDepth = info.get("startDepth") is int?(int)info.get("startDepth"):-1;
				int endDepth = info.get("endDepth") is int?(int)info.get("endDepth"):-1;
				if(startDepth != -1 && startDepth == endDepth)
					sql.Append("nc.depth = ").Append(startDepth).Append(" AND ");
				else if(startDepth != -1 && endDepth != -1)
					sql.AppendFormat("nc.depth >= {0} AND nc.depth <= {1} AND ", startDepth, endDepth);
			]]></code>
			<code><![CDATA[
				bool hasCriteria = false;
				string firstPerc = "", secondPerc = "", c="x";
				if(criteria != null && ((IBizObjList)criteria.get("filterOptions")).numElements != 0)
				{
					sql.Append("(");
					foreach(IBizObj f in criteria.get("filterOptions") as IBizObjList)
					{
						c = f.get("criteria") as string;
						if (string.Compare(c, "s", true) == 0)
							firstPerc = "%";
						else if (string.Compare(c, "e", true) == 0)
							secondPerc = "%";
						else if (string.Compare(c, "c", true) == 0)
							firstPerc = secondPerc = "%";
						if (firstPerc.Length > 0 || secondPerc.Length > 0)
							c = "like";
						if(f.get("field") != null && (string)f.get("field") != "")
						{
							hasCriteria = true;
							sql.AppendFormat(" {0} {1} '{3}{2}{4}'", f.get("field"), c, f.get("value"), firstPerc, secondPerc);
							sql.Append(criteria.get("filterIsAnd").Equals(true)?" AND":"  OR");
						}
					}
					if(hasCriteria)
					{
						sql.Length -= 4;
						sql.Append(") AND ");
					}
					else
						sql.Length -= 1;
				}
			]]></code>
			a.companyId = @companyId
			<code><![CDATA[
				if(info.get("objectTreeName") is string)
				{
					sql.Append(" AND treeName = @treeName ");
					addParameter(cmd,"objectTreeName","treeName","VarChar", info);
				}
				if(!parentIdProvided || !entireTree)
					sql.Append(" AND t.objectId = t.parentHostId ");
			]]></code>
			
	</Sql>
	<Param name="parentId" type="UniqueIdentifier" />
	<Param name="companyId" type="UniqueIdentifier" />
</AdoNetDataAccess>
